package com.kaikeba.dao.imp;

import com.kaikeba.bean.User;
import com.kaikeba.bean.User;
import com.kaikeba.dao.BaseUserDao;
import com.kaikeba.util.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDaoMysql implements BaseUserDao {

    private static final String SQL_CONSOLE = "SELECT COUNT(ID) SUM, COUNT(TO_DAYS(REGISTERTIME)=TO_DAYS(NOW()) OR NULL) DAY FROM USER";
    private static final String SQL_INSERT = "INSERT INTO USER(ID, USERNAME, PHONE, PASSWORD, REGISTERTIME, LASTLOGINTIME) VALUES(null, ?, ?, ?, NOW(), null)";
    private static final String SQL_DELETE = "DELETE FROM USER WHERE ID=?";
    private static final String SQL_DELETE_BY_PHONE = "DELETE FROM USER WHERE PHONE=?";
    private static final String SQL_UPDATE = "UPDATE USER SET USERNAME=?, PHONE=?, PASSWORD=? WHERE PHONE=?";
    private static final String SQL_FIND_ALL = "SELECT ID, USERNAME, PHONE, PASSWORD, REGISTERTIME, LASTLOGINTIME FROM USER";
    private static final String SQL_FIND_LIMIT = "SELECT ID, USERNAME, PHONE, PASSWORD, REGISTERTIME, LASTLOGINTIME FROM USER LIMIT ?, ?";
    private static final String SQL_FIND_BY_PHONE = "SELECT ID, USERNAME, PHONE, PASSWORD, REGISTERTIME, LASTLOGINTIME FROM USER WHERE PHONE=?";

    @Override
    public Map<String, Integer> console() {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            rs = state.executeQuery();
            Map<String, Integer> result = new HashMap<>();
            if(rs.next()){
                result.put("sum", rs.getInt("SUM"));
                result.put("day", rs.getInt("DAY"));
                return result;
            }else{
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, rs);
        }
        return null;
    }

    @Override
    public boolean insert(User user) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            state.setString(1, user.getUsername());
            state.setString(2, user.getPhone());
            state.setString(3, user.getPassword());
            int result = state.executeUpdate();
            if(result > 0){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return false;
    }

    @Override
    public boolean delete(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setInt(1, id);
            int result = state.executeUpdate();
            if(result > 0){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return false;
    }

    @Override
    public boolean deleteByPhone(String phone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE_BY_PHONE);
            state.setString(1, phone);
            int result = state.executeUpdate();
            if(result > 0){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return false;
    }

    @Override
    public boolean updateByPhone(String phone, User user) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1, user.getUsername());
            state.setString(2, user.getPhone());
            state.setString(3, user.getPassword());
            state.setString(4, phone);
            int result = state.executeUpdate();
            if(result > 0){
                return true;
            }else{
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return false;
    }

    @Override
    public List<User> findAll(int limit, int offset, int pageNumber) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        List<User> list = new ArrayList<>();
        try {
            if(limit == 0){
                state = conn.prepareStatement(SQL_FIND_ALL);
            }else{
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                state.setInt(1, offset);
                state.setInt(2, pageNumber);
            }
            rs = state.executeQuery();
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt("ID"));
                user.setUsername(rs.getString("USERNAME"));
                user.setPhone(rs.getString("PHONE"));
                user.setPassword(rs.getString("PASSWORD"));
                user.setRegisterTime(rs.getTimestamp("REGISTERTIME"));
                user.setLastLoginTime(rs.getTimestamp("LASTLOGINTIME"));
                list.add(user);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return null;
    }

    @Override
    public User findByPhone(String phone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONE);
            state.setString(1, phone);
            rs = state.executeQuery();
            if(rs.next()){
                User user = new User();
                user.setId(rs.getInt("ID"));
                user.setUsername(rs.getString("USERNAME"));
                user.setPhone(rs.getString("PHONE"));
                user.setPassword(rs.getString("PASSWORD"));
                user.setRegisterTime(rs.getTimestamp("REGISTERTIME"));
                user.setLastLoginTime(rs.getTimestamp("LASTLOGINTIME"));
                return user;
            }else{
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state);
        }
        return null;
    }
}
